﻿-- Function: pl_function.guardar_vpredio(character varying, character varying, character varying)

-- DROP FUNCTION pl_function.guardar_vpredio(character varying, character varying, character varying);

CREATE OR REPLACE FUNCTION pl_function.guardar_vpredio(p_mhresum character varying)
  RETURNS integer AS
$BODY$
declare
	ids numeric(18, 0) := 0;
	p_vhostnm character varying(25);
	p_vusernm character varying(25);
begin
	select vhostnm, vusernm into p_vhostnm, p_vusernm from registro.mhresum where idsigma=p_mhresum;
	select coalesce(max(idsigma)::numeric(18, 0), 0) into ids from registro.vpredio; 

	with vconstr(dpredio, cperiod, nvalpis, narecon) as (
		select a.dpredio, b.cperiod, sum(b.nvalpis) nvalpis, sum(a.narecon) narecon
		from registro.dpredio d
			inner join registro.mconstr a on d.idsigma=a.dpredio
			inner join registro.dconstr b on a.idsigma=b.mconstr
		where d.mhresum=p_mhresum and a.nestado=1
		group by a.dpredio, b.cperiod
	), vinstal(dpredio, cperiod, nvalins) as (
		select a.dpredio, b.cperiod, sum(b.nvalins) nvalins
		from registro.dpredio d 
			inner join registro.minstal a on d.idsigma=a.dpredio
			inner join registro.dinstal b on a.idsigma=b.minstal
		where d.mhresum=p_mhresum and a.nestado=1
		group by a.dpredio, b.cperiod
	)
	insert into registro.vpredio(
		  idsigma, dpredio, cperiod, narance
		, nvalpis, nvalins, nvalter, nvalpre
		, nporafe, nvalafe, nestado, vhostnm
		, vusernm, ddatetm, nbarrid, nparque
		, nrecole, nserena
	)
	select 
		lpad(((row_number() over(order by a.idsigma)) + ids)::varchar, 10, '0') idsigma
		, a.idsigma dpredio
		, c.cperiod
		, c.narance
		, coalesce(e.nvalpis, 0.00) nvalpis
		, coalesce(f.nvalins, 0.00) nvalins
		, trunc(a.nterren * c.narance, 2) nvalter
		, coalesce(e.nvalpis, 0.00)
		+ coalesce(f.nvalins, 0.00)
		+ trunc(a.nterren * c.narance, 2) nvalpre
		, a.nporafe
		, trunc(
		( coalesce(e.nvalpis, 0.00)
		+ coalesce(f.nvalins, 0.00)
		+ trunc(a.nterren * c.narance, 2) )
		* (a.nporafe / 100), 2)  nvalafe
		, 1 nestado
		, p_vhostnm
		, p_vusernm
		, now() ddatetm
		, round(trunc(coalesce(a.nfrente 
		* c.nfacbar 
		* (case when g.nporcen <= 0.00 then 100.00 else g.nporcen end / 100.00), 0.000), 3) * 12, 2) nbarrid
		, round(trunc(coalesce(h.ntasmen 
		* (case when g.nporcen <= 0.00 then 100.00 else g.nporcen end / 100.00), 0.000), 3) * 12, 2) nparque
		, round(trunc(coalesce(e.narecon * j.ntasmen 
		* (case when g.nporcen <= 0.00 then 100.00 else g.nporcen end / 100.00), 0.000), 3) * 12, 2) nrecole
		, round(trunc(coalesce(k.ntasmen
		* (case when g.nporcen <= 0.00 then 100.00 else g.nporcen end / 100.00), 0.000), 3) * 12, 2) nserena
	from registro.dpredio a
		inner join registro.mpredio b on a.mpredio=b.idsigma
		inner join registro.mpropie g on a.mpredio=g.mpredio and a.mhresum=g.mhresum and g.nestado=1 and g.ctippro='1000000451'
		inner join registro.marance c on b.mviadis=c.mviadis and c.nestado=1
		inner join registro.mhresum d on a.mhresum=d.idsigma
		inner join registro.mpoblad l on c.mpoblad=l.idsigma
		left join vconstr e on a.idsigma=e.dpredio and c.cperiod=e.cperiod
		left join vinstal f on a.idsigma=f.dpredio and c.cperiod=f.cperiod
		left join registro.pararbi h on c.cperiod=h.cperiod and a.ctippar=h.cidconten and h.ctiprec='0000000283' and l.cidzona=h.cidzona
		left join registro.pararbi j on c.cperiod=j.cperiod and a.cusogen=j.cidconten and j.ctiprec='0000007267'
		left join registro.pararbi k on c.cperiod=k.cperiod and a.cusogen=k.cidconten and k.ctiprec='0000008509' and l.cidzona=k.cidzona
	where d.idsigma=p_mhresum and c.cperiod::int >= d.cperini::int and c.cperiod::int <= coalesce(cperfin, to_char(now(), 'yyyy'))::int;

	select pl_function.guardar_dresumn(p_mhresum, p_vhostnm, p_vusernm) into ids;
	return ids;
exception
when sqlstate '57014' then
	raise notice 'Error';
	return 0;
end
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION pl_function.guardar_vpredio(character varying, character varying, character varying)
  OWNER TO postgres;
COMMENT ON FUNCTION pl_function.guardar_vpredio(character varying, character varying, character varying) IS '  ';
